package com.github.wxhnyfy.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.github.wxhnyfy.ExcelData;
import com.github.wxhnyfy.NoHeaderSheetData;
import com.github.wxhnyfy.SheetData;
import com.github.wxhnyfy.service.ExcelToolService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.dhatim.fastexcel.reader.ReadableWorkbook;
import org.dhatim.fastexcel.reader.Sheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.InputStream;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Stream;

/**
 * Easy Excel工具类
 *
 * @author chenwc
 */
public class EasyExcelTool implements ExcelToolService {

    private static final Logger logger = LoggerFactory.getLogger(EasyExcelTool.class);

    /**
     * 获取头样式
     *
     * @return 头样式
     */
    private static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.AUTOMATIC.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteFont.setFontName("宋体");
        headWriteFont.setColor(IndexedColors.RED.getIndex());
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, new WriteCellStyle());
    }

    /**
     * 读取Excel文件第 1 个Sheet
     *
     * @param excelFile Excel文件
     * @return sheet数据
     */
    @Override
    public SheetData read(File excelFile) {
        return read(excelFile, 0);
    }

    /**
     * 读取整个Excel文件
     *
     * @param excelFile Excel文件
     * @return Excel数据
     */
    @Override
    public ExcelData readExcelFile(File excelFile) {
        ExcelData excelData = new ExcelData();
        excelData.setFileName(excelFile.getName());
        List<SheetData> sheetDataList = new ArrayList<>();

        Map<Integer, String> sheetNameMap = getSheetNameMap(excelFile);
        for (Map.Entry<Integer, String> entry : sheetNameMap.entrySet()) {
            SheetData sheetData = read(excelFile, entry.getKey());
            sheetData.setSheetName(entry.getValue());
            sheetDataList.add(sheetData);
        }
        excelData.setSheetNameMap(sheetNameMap);
        excelData.setSheetSize(sheetDataList.size());
        excelData.setSheetDataList(sheetDataList);
        return excelData;
    }

    /**
     * 指定Sheet名称读取Excel文件
     *
     * @param excelFile Excel文件
     * @param sheetName Sheet名称
     * @return sheet数据
     */
    @Override
    public SheetData read(File excelFile, String sheetName) {
        ArrayDataListener listener = new ArrayDataListener();
        try (ExcelReader excelReader = EasyExcel.read(excelFile.getAbsolutePath(), listener).build()) {
            // 构建一个sheet 这里可以指定名字或者no
            ReadSheet readSheet = EasyExcel.readSheet(sheetName).build();
            // 读取一个sheet
            excelReader.read(readSheet);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return listener.getSheetData();
    }

    /**
     * 读取Excel文件第 index 个Sheet
     *
     * @param excelFile  Excel文件
     * @param sheetIndex Sheet下标
     * @return sheet数据
     */
    @Override
    public SheetData read(File excelFile, int sheetIndex) {
        ArrayDataListener listener = new ArrayDataListener();
        try (ExcelReader excelReader = EasyExcel.read(excelFile.getAbsolutePath(), listener).build()) {
            // 构建一个sheet 这里可以指定名字或者no
            ReadSheet readSheet = EasyExcel.readSheet(sheetIndex).build();
            // 读取一个sheet
            excelReader.read(readSheet);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return listener.getSheetData();
    }

    /**
     * 读取excel文件，没有表头的sheet
     *
     * @param excelFile  excel文件
     * @param sheetIndex 要读取的sheet页签下标
     * @return excel文件数据
     */
    @Override
    public SheetData readByNoHeaderSheet(File excelFile, int sheetIndex) {

        ReadNoHeaderSheetListener listener = new ReadNoHeaderSheetListener();
        try (ExcelReader excelReader = EasyExcel.read(excelFile.getAbsolutePath(), listener).build()) {
            // 构建一个sheet 这里可以指定名字或者no
            ReadSheet readSheet = EasyExcel.readSheet(sheetIndex).build();
            // 读取一个sheet
            excelReader.read(readSheet);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return listener.getSheetData();
    }

    /**
     * 读取excel文件，没有表头的sheet
     *
     * @param excelFile  excel文件
     * @param sheetIndex 要读取的sheet页签下标
     * @return excel文件数据
     */
    @Override
    public NoHeaderSheetData readByNoHeaderSheetData(File excelFile, int sheetIndex) {
        ReadNoHeaderSheetToListListener listener = new ReadNoHeaderSheetToListListener();
        try (ExcelReader excelReader = EasyExcel.read(excelFile.getAbsolutePath(), listener).headRowNumber(0).build()) {
            // 构建一个sheet 这里可以指定名字或者no
            ReadSheet readSheet = EasyExcel.readSheet(sheetIndex).build();
            // 读取一个sheet
            excelReader.read(readSheet);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return listener.getSheetData();
    }

    /**
     * 自定义读取excel文件的sheet
     *
     * @param excelFile   excel文件
     * @param sheetIndex  要读取的sheet页签下标（从0开始）
     * @param startRow    开始读取的行号（从0开始）
     * @param endRow      结束读取的行号（从0开始）
     * @param startColumn 开始读取的列数（从0开始）
     * @param endColumn   结束读取的总列数（从0开始）
     * @return excel文件数据
     */
    @Override
    public SheetData read(File excelFile, int sheetIndex, int startRow, int endRow, int startColumn, int endColumn) {
        ReadSheetByRangeListener listener = new ReadSheetByRangeListener(startRow, endRow, startColumn, endColumn);
        try (ExcelReader excelReader = EasyExcel.read(excelFile.getAbsolutePath(), listener).headRowNumber(0).build()) {
            // 构建一个sheet 这里可以指定名字或者no
            ReadSheet readSheet = EasyExcel.readSheet(sheetIndex).build();
            // 读取一个sheet
            excelReader.read(readSheet);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return listener.getSheetData();
    }

    /**
     * 写入Excel文件
     *
     * @param excelFile Excel文件
     * @param sheetData Sheet数据
     */
    @Override
    public void write(File excelFile, SheetData sheetData) {
        logger.info("开始写入文件...........");
        long start = System.currentTimeMillis();
        // 头的策略
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = getHorizontalCellStyleStrategy();
        if (StringUtils.isEmpty(sheetData.getSheetName())) {
            sheetData.setSheetName("Sheet1");
        }
        EasyExcel.write(excelFile)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .registerWriteHandler(new FreezeAndFilterHandler(1, 0, 1, "1:1"))
                .head(toExcelHeader(sheetData.getHeader())).sheet(sheetData.getSheetName())
                .doWrite(toExcelData(sheetData.getDataMapList(), sheetData.getHeader()));
        logger.info("写入文件结束...........");
        long end = System.currentTimeMillis();
        logger.info("写入耗时: {} ms", (end - start));
    }

    /**
     * 写入Excel文件
     *
     * @param excelFile Excel文件
     * @param sheetData Sheet数据
     */
    @Override
    public void writeNoHeaderSheet(File excelFile, NoHeaderSheetData sheetData) {
        logger.info("开始写入文件...........");
        long start = System.currentTimeMillis();
        if (StringUtils.isEmpty(sheetData.getSheetName())) {
            sheetData.setSheetName("Sheet1");
        }
        EasyExcel.write(excelFile)
                .sheet(sheetData.getSheetName())
                .doWrite(sheetData.getDataList());
        logger.info("写入文件结束...........");
        long end = System.currentTimeMillis();
        logger.info("写入耗时: {} ms", (end - start));
    }

    /**
     * 同一个Excel文件写入多个Sheet
     * 每个工作表由不同的线程生成
     *
     * @param excelFile     Excel文件
     * @param sheetDataList Sheet数据
     */
    @Override
    public void writeMultipleSheet(File excelFile, List<SheetData> sheetDataList) {
        logger.info("开始写入文件...........");
        long start = System.currentTimeMillis();
        try (ExcelWriter excelWriter = EasyExcel.write(excelFile).build()) {
            // 头的策略
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = getHorizontalCellStyleStrategy();
            FreezeAndFilterHandler freezeAndFilterHandler = new FreezeAndFilterHandler(1, 0, 1, "1:1");
            for (int i = 0; i < sheetDataList.size(); i++) {
                SheetData writeSheetData = sheetDataList.get(i);
                if (StringUtils.isEmpty(writeSheetData.getSheetName())) {
                    writeSheetData.setSheetName("Sheet" + (i + 1));
                }
                // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
                WriteSheet regionWriteSheet = EasyExcel.writerSheet(i, writeSheetData.getSheetName())
                        .head(toExcelHeader(writeSheetData.getHeader()))
                        .registerWriteHandler(horizontalCellStyleStrategy)
                        .registerWriteHandler(freezeAndFilterHandler)
                        .build();
                excelWriter.write(toExcelData(writeSheetData.getDataMapList(), writeSheetData.getHeader()), regionWriteSheet);
            }
            excelWriter.finish();
            logger.info("写入文件结束...........");
            long end = System.currentTimeMillis();
            logger.info("写入耗时: {} ms", (end - start));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 转为excel表头
     *
     * @return excel表头
     */
    public List<List<String>> toExcelHeader(List<String> header) {
        List<List<String>> headList = new ArrayList<>();
        for (String row : header) {
            List<String> h = new ArrayList<>();
            h.add(row);
            headList.add(h);
        }
        return headList;
    }

    /**
     * 把 List<Map<String, String>> 转换为可以写入excel的List<List<String>>数据
     *
     * @return List<List < String>>
     */
    public List<List<String>> toExcelData(List<Map<String, String>> dataMapList, List<String> header) {
        List<List<String>> list = new ArrayList<>();
        for (Map<String, String> dataMap : dataMapList) {
            List<String> data = new ArrayList<>();
            for (String s : header) {
                data.add(dataMap.get(s));
            }
            list.add(data);
        }
        return list;
    }

    /**
     * 获取Excel文件的所有Sheet
     * 阿里的EasyExcel没有读取所有Sheet的方法，这里用的是fastexcel
     *
     * @param excelFile Excel文件
     * @return Excel文件的Sheet名称
     */
    private Map<Integer, String> getSheetNameMap(File excelFile) {
        Map<Integer, String> sheetNameMap = new HashMap<>();
        try (InputStream is = Files.newInputStream(excelFile.toPath()); ReadableWorkbook wb = new ReadableWorkbook(is)) {
            Stream<Sheet> sheets = wb.getSheets();
            AtomicInteger cnt = new AtomicInteger();
            sheets.forEach(sheet -> {
                int sheetIndex = cnt.getAndIncrement();
                sheetNameMap.put(sheetIndex, sheet.getName());
            });
        } catch (Exception e) {
            logger.error("读取Excel文件失败", e);
        }
        return sheetNameMap;
    }

    /**
     * 筛选和冻结行
     */
    static class FreezeAndFilterHandler implements SheetWriteHandler {

        // 要冻结的行数（从0开始计数，表示第一行）
        private int rowSplit;
        // 右窗格中可见的左列（通常设置为0，表示不偏移）
        private int leftmostColumn;
        // 底部窗格中可见的顶行（通常与rowSplit相同，表示冻结行下方的第一行）
        private int topRow;
        // 筛选范围（例如"1:1"表示第一行作为筛选框的位置）
        private String autoFilterRange;

        /**
         * 构造函数
         *
         * @param rowSplit        要冻结的行数（从0开始计数，表示第一行）
         * @param leftmostColumn  右窗格中可见的左列（通常设置为0，表示不偏移）
         * @param topRow          底部窗格中可见的顶行（通常与rowSplit相同，表示冻结行下方的第一行）
         * @param autoFilterRange 筛选范围（例如"1:1"表示第一行作为筛选框的位置）
         */
        private FreezeAndFilterHandler(int rowSplit, int leftmostColumn, int topRow, String autoFilterRange) {
            this.rowSplit = rowSplit;
            this.leftmostColumn = leftmostColumn;
            this.topRow = topRow;
            this.autoFilterRange = autoFilterRange;
        }

        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            // 在创建sheet之前可以执行一些操作，但此处通常不需要
        }

        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            org.apache.poi.ss.usermodel.Sheet sheet = writeSheetHolder.getSheet();
            // 设置冻结行
            sheet.createFreezePane(0, rowSplit, leftmostColumn, topRow);
            // 设置筛选范围
            sheet.setAutoFilter(CellRangeAddress.valueOf(autoFilterRange));
        }
    }

    /**
     * 读取Excel文件，按范围读取sheet
     */
    static class ReadSheetByRangeListener implements ReadListener<Map<Integer, String>> {
        private final Logger logger = LoggerFactory.getLogger(ReadSheetByRangeListener.class);
        private SheetData sheetData = new SheetData();
        private List<Map<String, String>> dataMapList = new ArrayList<>();
        private List<String> header = new ArrayList<>();
        private AtomicInteger cnt = new AtomicInteger();
        private int startRow;
        private int endRow;
        private int startColumn;
        private int endColumn;

        public ReadSheetByRangeListener(int startRow, int endRow, int startColumn, int endColumn) {
            this.startRow = startRow;
            this.endRow = endRow;
            this.startColumn = startColumn;
            this.endColumn = endColumn;
        }

        /**
         * When analysis one row trigger invoke function.
         *
         * @param data    one row value. It is same as {@link AnalysisContext#readRowHolder()}
         * @param context analysis context
         */
        @Override
        public void invoke(Map<Integer, String> data, AnalysisContext context) {

            int rowNumber = cnt.getAndIncrement();
            if (rowNumber == 0) {
                for (int i = startColumn; i <= endColumn; i++) {
                    header.add(String.valueOf(i));
                }
            }
            if (rowNumber >= startRow && rowNumber < endRow) {
                Map<String, String> dataMap = new HashMap<>();
                for (Integer entry : data.keySet()) {
                    if (entry >= startColumn && entry <= endColumn) {
                        String value = data.get(entry);
                        if (StringUtils.isEmpty(value)) {
                            value = "";
                        }
                        dataMap.put(String.valueOf(entry), value);
                    }
                }
                if (!dataMap.isEmpty()) {
                    dataMapList.add(dataMap);
                }
            }
        }

        /**
         * if have something to do after all analysis
         *
         * @param context analysis context
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            logger.debug("读取完成！");
            sheetData.setSheetName(context.readSheetHolder().getSheetName());
            sheetData.setHeader(header);
            sheetData.setDataMapList(dataMapList);
        }

        public SheetData getSheetData() {
            return sheetData;
        }
    }

    /**
     * 读取Excel文件，没有表头的sheet
     */
    static class ReadNoHeaderSheetToListListener implements ReadListener<Map<Integer, String>> {
        private final Logger logger = LoggerFactory.getLogger(ReadNoHeaderSheetToListListener.class);
        private NoHeaderSheetData sheetData = new NoHeaderSheetData();
        private List<List<String>> dataList = new ArrayList<>();

        /**
         * When analysis one row trigger invoke function.
         *
         * @param data    one row value. It is same as {@link AnalysisContext#readRowHolder()}
         * @param context analysis context
         */
        @Override
        public void invoke(Map<Integer, String> data, AnalysisContext context) {

            List<String> line = new ArrayList<>();
            for (Integer entry : data.keySet()) {
                String value = data.get(entry);
                if (StringUtils.isEmpty(value)) {
                    value = "";
                }
                line.add(value);
            }
            dataList.add(line);
        }

        /**
         * if have something to do after all analysis
         *
         * @param context analysis context
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            logger.debug("读取完成！");
            sheetData.setSheetName(context.readSheetHolder().getSheetName());
            sheetData.setDataList(dataList);
        }

        public NoHeaderSheetData getSheetData() {
            return sheetData;
        }
    }

    /**
     * 读取Excel文件，没有表头的sheet
     */
    static class ReadNoHeaderSheetListener implements ReadListener<Map<Integer, String>> {
        private final Logger logger = LoggerFactory.getLogger(ReadNoHeaderSheetListener.class);
        private SheetData sheetData = new SheetData();
        private List<Map<String, String>> dataMapList = new ArrayList<>();
        private List<String> header = new ArrayList<>();

        /**
         * 读取表头
         *
         * @param headMap 表头map
         * @param context 上下文1
         */
        @Override
        public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
            for (Integer entry : headMap.keySet()) {
                header.add(String.valueOf(entry));
            }
            Map<String, String> dataMap = new HashMap<>();
            for (Integer entry : headMap.keySet()) {
                ReadCellData<?> cellData = headMap.get(entry);
                String value = cellData.getStringValue();
                if (StringUtils.isEmpty(value)) {
                    value = "";
                }
                dataMap.put(String.valueOf(entry), value);
            }
            dataMapList.add(dataMap);
        }

        /**
         * When analysis one row trigger invoke function.
         *
         * @param data    one row value. It is same as {@link AnalysisContext#readRowHolder()}
         * @param context analysis context
         */
        @Override
        public void invoke(Map<Integer, String> data, AnalysisContext context) {

            Map<String, String> dataMap = new HashMap<>();
            for (Integer entry : data.keySet()) {
                String value = data.get(entry);
                if (StringUtils.isEmpty(value)) {
                    value = "";
                }
                dataMap.put(String.valueOf(entry), value);
            }
            dataMapList.add(dataMap);
        }

        /**
         * if have something to do after all analysis
         *
         * @param context analysis context
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            logger.debug("读取完成！");
            sheetData.setSheetName(context.readSheetHolder().getSheetName());
            sheetData.setHeader(header);
            sheetData.setDataMapList(dataMapList);
        }

        public SheetData getSheetData() {
            return sheetData;
        }
    }

    /**
     * 读取监听器
     *
     * @author chenwc
     */
    static class ArrayDataListener extends AnalysisEventListener<Map<Integer, String>> {
        private final Logger logger = LoggerFactory.getLogger(ArrayDataListener.class);
        private SheetData sheetData = new SheetData();
        private List<Map<String, String>> dataMapList = new ArrayList<>();
        private Map<Integer, String> headerMap = new HashMap<>();
        private List<String> header = new ArrayList<>();

        /**
         * 读取表头
         *
         * @param headMap 表头map
         * @param context 上下文1
         */
        @Override
        public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
            super.invokeHead(headMap, context);
            for (Integer key : headMap.keySet()) {
                ReadCellData<?> cellData = headMap.get(key);
                String value = cellData.getStringValue();
                if (StringUtils.isEmpty(value)) {
                    value = "";
                }
                headerMap.put(key, value);
                header.add(value);
            }
            logger.debug("读取到的表头为: {}", header);
        }

        /**
         * 读取行数据
         *
         * @param dataMap         数据map
         * @param analysisContext 上下文
         */
        @Override
        public void invoke(Map<Integer, String> dataMap, AnalysisContext analysisContext) {
            Map<String, String> line = new HashMap<>();
            for (Integer key : dataMap.keySet()) {
                String value = dataMap.get(key);
                if (StringUtils.isEmpty(value)) {
                    value = "";
                }
                line.put(headerMap.get(key), value);
            }
            dataMapList.add(line);
        }

        /**
         * 读取完成
         *
         * @param analysisContext 上下文
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            logger.debug("读取完成");
            sheetData.setSheetName(analysisContext.readSheetHolder().getSheetName());
            sheetData.setHeader(header);
            sheetData.setDataMapList(dataMapList);
        }

        public SheetData getSheetData() {
            return sheetData;
        }
    }
}
